<?php

session_start();
include '../inc/db_util.php';

$action = $_GET['act'];

switch ($action) {
    case 'add':
        create();
        break;
    case 'upd':
        update();
        break;
    case 'del':
        delete();
        break;
    case 'approve':
        approve();
        break;
    case 'denie':
        reject();
        break;
    case 'eliminate':
        eliminate();
        break;
    case 'list':
        getListKuliah();
        break;
    case 'get_mykuliah':
        getKuliahDosen();
        break;
    case 'get_exkuliah':
        getAvailableKuliah();
        break;
    case 'get_dosen':
        getDosen();
        break;
    case 'getjoined':
        getJoined();
        break;
    case 'getunapproved':
        getUnApproved();
        break;
    default:
        echo 'Wrong command!';
        break;
}

function create() {
    $nip = $_POST['namadosen'];
    $id_matkul = $_POST['matkul'];
    $tahun = $_POST['tahun_ajaran'];

    $query = "INSERT INTO tb_det_matkul VALUES(null,'$nip','$id_matkul', (SELECT semester from tb_matkul_master WHERE id_matkul='$id_matkul'),'$tahun')";

    $result = mysql_query($query) or die(mysql_error());
    if ($result) {
        echo "Perkuliahan baru telah di buka";
    }
}

function update() {
    $id_det_matkul = $_POST['id_det_matkul'];
    $id_matkul = $_POST['id_matkul'];
    $nip = $_POST['nip'];
    $tahun = $_POST['tahun_ajaran'];
	
	$value = $id_matkul == NULL ? "" : ", id_matkul='$id_matkul' ";
	$value .= $nip == NULL ? "" : ", nip='$nip' ";
	$value .= $tahun == NULL ? "" : ", tahun_ajaran='$tahun' ";

    $query = "UPDATE tb_det_matkul SET id_det_matkul='$id_det_matkul' $value
                    WHERE id_det_matkul='$id_det_matkul'";

    $result = mysql_query($query) or die(mysql_error());
    if ($result) {
        echo "Perkuliahan telah di update";
    }
}

function delete() {
    $id_det_matkul = $_POST['id_det_matkul'];
    $array = json_decode($id_det_matkul);
    $ids = implode("','", $array);

	$qcek = "SELECT id_det_matkul FROM tb_perkuliahan WHERE id_det_matkul IN ('$ids')";
    $cek = mysql_query($qcek) or die(mysql_error());
	
	if (mysql_num_rows($cek)) {
		echo "Perkuliahan yang sudah berjalan tidak dapat dihapus";
	} else {
		$query = "DELETE FROM tb_det_matkul WHERE id_det_matkul IN ('$ids') AND id_det_matkul NOT IN (SELECT id_det_matkul FROM tb_perkuliahan)";
		$result = mysql_query($query) or die(mysql_error());
		
		$count = count($array) > 1 ? "(" . count($array) . ") " : "";

		if ($result) {
			echo $count . "Perkuliahan telah dihapus";
		} else {
			echo "Gagal menghapus " . $count . " data yang di pilih!";
		}
	}
}

function getKuliahDosen() {
    $nip = $_SESSION['nip'];

    $where = " WHERE nip = '$nip' ";

    $query = "SELECT dm.id_det_matkul, nip, dm.semester, tahun_ajaran, matkul, sks 
        FROM tb_det_matkul dm
        JOIN tb_matkul_master mm ON mm.id_matkul = dm.id_matkul
        $where";

    $result = array();
    $row = array();

    $criteria = mysql_query($query);

    while ($data = mysql_fetch_array($criteria)) {
        $row[] = array(
            'id_det_matkul' => $data['id_det_matkul'],
            'tahun_ajaran' => $data['tahun_ajaran'],
            'matkul' => $data['matkul'],
            'sks' => $data['sks'],
            'semester' => $data['semester']
        );
    }
    $result = array_merge($result, $row);
    echo json_encode($result);
}

function getListKuliah() {
    $page = isset($_POST['page']) ? intval($_POST['page']) : 1;
    $rows = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
    $sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'kode_matkul, matkul';
    $order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';
    $cari = isset($_POST['cari']) ? mysql_real_escape_string($_POST['cari']) : '';

    $offset = ($page - 1) * $rows;

    $where = "WHERE kode_matkul LIKE '%$cari%' OR matkul LIKE '%$cari%' "
            . " OR namadosen LIKE '%$cari%' OR sks = '%$cari%' OR tahun_ajaran = '%$cari%'";

    $query = "SELECT dm.id_det_matkul, mm.id_matkul, mm.kode_matkul, tb.nip, namadosen, dm.semester, tahun_ajaran, matkul, sks 
        FROM tb_det_matkul dm
        JOIN tb_matkul_master mm ON mm.id_matkul = dm.id_matkul
		JOIN tb_dosen tb ON dm.nip=tb.nip
        $where
		ORDER BY $sort $order
		LIMIT $rows OFFSET $offset";

    $result = array();
    $result['total'] = mysql_num_rows(mysql_query($query));
    $row = array();

    $criteria = mysql_query($query) or die(mysql_error());

    while ($data = mysql_fetch_array($criteria)) {
        $row[] = array(
            'id_det_matkul' => $data['id_det_matkul'],
            'id_matkul' => $data['id_matkul'],
            'kode_matkul' => $data['kode_matkul'],
            'matkul' => $data['matkul'],
            'tahun_ajaran' => $data['tahun_ajaran'],
            'sks' => $data['sks'],
            'semester' => $data['semester'],
            'nip' => $data['nip'],
            'namadosen' => $data['namadosen']
        );
    }
    $result = array_merge($result, array('rows' => $row));
    echo json_encode($result);
}

function getAvailableKuliah() {
    $where = " WHERE id_matkul NOT IN (SELECT id_matkul FROM tb_det_matkul) ";

    $query = "SELECT id_matkul, kode_matkul, matkul, sks, semester 
        FROM tb_matkul_master $where ORDER by matkul";

    $result = array();
    $row = array();

    $criteria = mysql_query($query);

    while ($data = mysql_fetch_array($criteria)) {
        $row[] = array(
            'id_matkul' => $data['id_matkul'],
            'kode_matkul' => $data['kode_matkul'],
            'matkul' => $data['matkul'],
            'sks' => $data['sks'],
            'semester' => $data['semester']
        );
    }
    $result = array_merge($result, $row);
    echo json_encode($result);
}

function getDosen() {
    $where = "";

    $query = "SELECT nip, namadosen
        FROM tb_dosen
        $where ORDER BY namadosen";

    $result = array();
    $row = array();

    $criteria = mysql_query($query);

    while ($data = mysql_fetch_array($criteria)) {
        $row[] = array(
            'nip' => $data['nip'],
            'nama' => $data['namadosen']
        );
    }
    $result = array_merge($result, $row);
    echo json_encode($result);
}

function getJoined() {
    $page = isset($_POST['page']) ? intval($_POST['page']) : 1;
    $rows = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
    $sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'nim';
    $order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';
    $cari = isset($_POST['cari']) ? mysql_real_escape_string($_POST['cari']) : '';
    $id_det_matkul = isset($_POST['id_det_matkul']) ? mysql_real_escape_string($_POST['id_det_matkul']) : '';

    $offset = ($page - 1) * $rows;

    $kuliah = "SELECT id_mahasiswa FROM tb_perkuliahan WHERE id_det_matkul= '$id_det_matkul'";

    $filter = " (nim LIKE '%$cari%' OR nama LIKE '%$cari%' OR alamat LIKE '%$cari%' "
            . " OR telepon LIKE '%$cari%' OR email LIKE '%$cari%' OR tahun_angkatan = '$cari')";

    $query = "SELECT * FROM tb_mahasiswa m
        LEFT JOIN tb_angkatan a ON a.id_angkatan = m.id_angkatan
        WHERE id_mahasiswa IN ( $kuliah ) AND $filter ";

    $paging = " ORDER BY $sort $order LIMIT $rows OFFSET $offset";

    $result = array();
    $count = mysql_query($query) or die(mysql_error());
    $result['total'] = mysql_num_rows($count);
    $row = array();

    $criteria = mysql_query($query . $paging) or die(mysql_error());

    while ($data = mysql_fetch_array($criteria)) {
        $row[] = array(
            'id' => $data['id_mahasiswa'],
            'nim' => $data['nim'],
            'nama' => $data['nama'],
            'alamat' => $data['alamat'],
            'telepon' => $data['telepon'],
            'email' => $data['email'],
            'angkatan' => $data['id_angkatan'],
            'tahun' => $data['tahun_angkatan'],
        );
    }
    $result = array_merge($result, array('rows' => $row));
    echo json_encode($result);
}

function getUnApproved() {
    $page = isset($_POST['page']) ? intval($_POST['page']) : 1;
    $rows = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
    $sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'nim';
    $order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';
    $cari = isset($_POST['cari']) ? mysql_real_escape_string($_POST['cari']) : '';
    $id_det_matkul = isset($_POST['id_det_matkul']) ? mysql_real_escape_string($_POST['id_det_matkul']) : '';

    $offset = ($page - 1) * $rows;

    $filter = " (nim LIKE '%$cari%' OR nama LIKE '%$cari%' OR alamat LIKE '%$cari%' "
            . " OR telepon LIKE '%$cari%' OR email LIKE '%$cari%' OR tahun_angkatan = '$cari')";

    $quey = "SELECT id_temp_perkuliahan, m.* FROM tb_temp_perkuliahan tp
        JOIN tb_mahasiswa m ON tp.id_mahasiswa=m.id_mahasiswa
        LEFT JOIN tb_angkatan a ON a.id_angkatan = m.id_angkatan
        WHERE id_det_matkul= '$id_det_matkul' AND $filter ";

    $paging = " ORDER BY $sort $order LIMIT $rows OFFSET $offset";

    $result = array();
    $count = mysql_query($quey) or die(mysql_error());
    $result['total'] = mysql_num_rows($count);
    $row = array();

    $criteria = mysql_query($quey . $paging) or die(mysql_error());

    while ($data = mysql_fetch_array($criteria)) {
        $row[] = array(
            'id' => $data['id_temp_perkuliahan'],
            'nim' => $data['nim'],
            'nama' => $data['nama'],
            'alamat' => $data['alamat'],
            'telepon' => $data['telepon'],
            'email' => $data['email'],
            'angkatan' => $data['id_angkatan'],
            'tahun' => $data['tahun_angkatan'],
        );
    }
    $result = array_merge($result, array('rows' => $row));
    echo json_encode($result);
}

function approve() {
    $ids = $_POST['id'];

    $array = json_decode($ids);
    $id = implode("','", $array);
    $count = count($array);

    $query = "INSERT INTO tb_perkuliahan(id_mahasiswa, id_det_matkul)
            SELECT t.id_mahasiswa, t.id_det_matkul
            FROM tb_temp_perkuliahan t WHERE id_temp_perkuliahan IN ('" . $id . "')";

    $result = mysql_query($query) or die(mysql_error());
    if ($result) {
        $success = mysql_query("DELETE FROM tb_temp_perkuliahan WHERE "
                . "id_temp_perkuliahan IN ('" . $id . "')") or die(mysql_error());

        if ($success) {
            echo $count . " Pendaftar telah di bergabung";
        }
    } else {
        echo "Gagal menerima $count pendaftar!";
    }
}

function reject() {
    $ids = $_POST['id'];

    $array = json_decode($ids);
    $id = implode("','", $array);

    $query = "DELETE FROM tb_temp_perkuliahan WHERE id_temp_perkuliahan IN ('" . $id . "')";
    
    $result = mysql_query($query) or die(mysql_error());

    $count = count($array) > 1 ? "(" . count($array) . ") " : "";

    if ($result) {
        echo $count . "Pendaftar yang di pilih telah ditolak";
    } else {
        echo "Gagal menghapus $count pendaftar!";
    }
}

function eliminate() {
    $ids = $_POST['id'];
    $id_mhs = $_POST['id_mhs'];

    $array = json_decode($id_mhs);
    $ids_mhs = implode("','", $array);

    $query3 = "DELETE FROM tb_perkuliahan WHERE id_mahasiswa IN ('" . $ids_mhs . "') AND id_det_matkul='" . $ids . "'";
	$query2 = "DELETE FROM tb_nilai_quis WHERE id_mahasiswa IN ('" . $ids_mhs . "') AND id_det_matkul='" . $ids . "'";
	$query1 = "DELETE FROM tb_det_tugas WHERE id_mahasiswa IN ('" . $ids_mhs . "') AND id_tugas IN 
					(SELECT id_tugas FROM tb_tugas WHERE id_det_matkul= '" . $ids . "')";
    
    $result1 = mysql_query($query1) or die(mysql_error());
	if($result1)
		$result2 = mysql_query($query2) or die(mysql_error());
	if($result2)
		$result3 = mysql_query($query3) or die(mysql_error());

    $count = count($array) > 1 ? "(" . count($array) . ") " : "";

    if ($result3) {
        echo $count . " Mahasiswa yang di pilih telah dikeluarkan";
    } else {
        echo "Gagal mengeluarkan $count mahasiswa!";
    }
}

?>